﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Security.Cryptography;
using LumenWorks.Framework.IO.Csv;

namespace OneMake.Excel
{
    public class ExcelHeleper
    {
        private const string XLS_CONN_STR = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
        private const string XLSX_CONN_STR = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;";

        //从Xls文件获取第一个Sheet名称
        //filePath,XLS文件路径
        public string GetSheetName(string filePath, int sheetIndex)
        {
            string[] sheetNames = GetXlsSheetNames(filePath);
            if (sheetNames.Length > sheetIndex)
                return sheetNames[sheetIndex];
            return null;
        }

        private string[] GetXlsSheetNames(string filePath)
        {
            string connString = GetConnString(filePath);
            using(OleDbConnection dbConn = new OleDbConnection(connString))
            {
                dbConn.Open();
                DataTable dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                    return null;

                List<string> excelSheets = new List<string>();
                for(int i=0;i<dt.Rows.Count;i++)
                {
                    string temp = dt.Rows[i]["TABLE_NAME"].ToString();
                    if (temp != "_xlnm#_FilterDatabase")
                        excelSheets.Add(temp);
                }

                return excelSheets.ToArray();
            }
        }

        //把XLS数据读取到DataReader
        //filePath,XLS文件路径
        public IDataReader ExecuteXlsReader(OleDbConnection dbConn, string sheetName)
        {
            string sql = string.Format("select * from [{0}]", sheetName);
            OleDbCommand cmd = new OleDbCommand(sql, dbConn);
            return cmd.ExecuteReader();
        }

        //获取Csv的DataReader
        public CsvReader ExecuteCsvReader(string fileName)
        {
            return new CsvReader(new StreamReader(fileName, Encoding.Default), true);
        }

        //计算文件MD5
        protected string GetFileMD5(string filePath)
        {
            using (FileStream file = new FileStream(filePath, FileMode.Open))
            {
                MD5 md5 = new MD5CryptoServiceProvider();
                byte[] retVal = md5.ComputeHash(file);
                file.Close();

                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < retVal.Length; i++)
                {
                    sb.Append(retVal[i].ToString("x2"));
                }
                return sb.ToString();
            }
        }

        //根据文件扩展名获取连接字符串
        public string GetConnString(string fileName)
        {
            FileInfo fi = new FileInfo(fileName);
            if (fi.Extension.ToUpper() == ".XLS")
                return string.Format(XLS_CONN_STR, fileName);
            if (fi.Extension.ToUpper() == ".XLSX")
                return string.Format(XLSX_CONN_STR, fileName);
            return null;
        }

    }
}
